package com.wangdao.mall.dao;

import com.wangdao.mall.model.*;
import com.wangdao.mall.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.math.BigInteger;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author:HaiyangTian
 * @Description:
 * @Date: Created in 10:37 2020/5/2
 * @Modified By:
 */
public class GoodsDaoImp implements GoodsDao{

    /**
     * 查询类目的列表
     * @return
     */
    @Override
    public List<Type> getType() {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        List<Type> typeList = null;
        try {
            typeList = runner.query("select * from type group by id",
                    new BeanListHandler<Type>(Type.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return typeList;
    }

    /**
     * 查询商品列表
     * @param typeId
     * @return
     */
    @Override
    public List<Goods> getGoodsByType(String typeId) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        List<Goods> goodsList = null;
        try {
            goodsList = runner.query("select * from goods where typeId = ?",
                    new BeanListHandler<Goods>(Goods.class),typeId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goodsList;
    }

    /**
     * 像goods表中添加商品
     * @param goodsParam
     * @return
     */
    @Override
    public int addGoods(GoodsParam goodsParam) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        try {
            runner.update("insert into goods values (null,?,?,?,?,?,?)",
                    goodsParam.getImg(),
                    goodsParam.getName(),
                    goodsParam.getPrice(),
                    goodsParam.getTypeId(),
                    goodsParam.getStockNum(),
                    goodsParam.getDesc());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        //因为id是一个自增的主键，插入完成后会有一个id，这个就是擅入商品的id
        //把这个id赋值给spec表的goodsId
        return insertId();
    }

    /**
     * 获取刚添加的商品id
     * @return
     */
    private int insertId(){
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        //last_insert_id()这个函数可以返回本次connect最后一次添加产生的id
        BigInteger goodsId = null;
        try {
            goodsId = runner.query("select last_insert_id()", new ScalarHandler<>());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goodsId.intValue();
    }

    /**
     * 将goodsId添加到specList中
     * @param goodsId
     * @param specList
     */
    @Override
    public void addSpecList(int goodsId, List<Spec> specList) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        for (Spec spec : specList) {
            try {
                runner.update("insert into spec values (null,?,?,?,?)",
                        spec.getSpecName(),
                        spec.getStockNum(),
                        spec.getUnitPrice(),
                        goodsId);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 在type表中增加这个分类
     * @param type
     * @return
     */
    @Override
    public int addType(Type type) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        int row = 0;
        try {
            row = runner.update("insert into type values (null,?)",
                    type.getName());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }

    @Override
    public int deleteType(String typeId) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        int row = 0;
        try {
            row = runner.update("delete from type where id = ?", typeId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }

    /**
     * 从商品表中删除数据
     * @param id
     * @return
     */
    @Override
    public void deleteGoods(String id) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        try {
            runner.update("delete from goods where id = ?", id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 从spec表中删除数据
     * @param id
     */
    @Override
    public void deleteSpecList(String id) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        try {
            runner.update("delete from spec where goodsId = ?", id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 从数据库获取商品信息
     * @param id
     * @return
     */
    @Override
    public Goods getGoods(String id) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        Goods goods = null;
        try {
            goods = runner.query("select * from goods where id = ?",
                    new BeanHandler<>(Goods.class), id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goods;
    }

    /**
     * 从数据库获取商品的规格信息
     * @param id
     * @return
     */
    @Override
    public List<Spec> getSpecs(String id) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        List<Spec> specs = null;
        try {
            specs = runner.query("select * from spec where goodsId = ?",
                    new BeanListHandler<Spec>(Spec.class), id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return specs;
    }

    /**
     * 在spec表中添加规格
     * @param spec
     * @return
     */
    @Override
    public int addSpec(SpecId spec) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        int row = 0;
        try {
            row = runner.update("insert into spec values(null,?,?,?,?)",
                    spec.getSpecName(),
                    spec.getStockNum(),
                    spec.getUnitPrice(),
                    spec.getGoodsId());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }

    /**
     * 在spec表中删除指定的规格
     * @param spec
     * @return
     */
    @Override
    public int deleteSpec(SpecId spec) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        int row = 0;
        try {
            row = runner.update("delete from spec where goodsId = ? and specName = ?",
                    spec.getGoodsId(),
                    spec.getSpecName());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }

    /**
     * 更新goods表中对应的数据
     * @param goodsParam
     */
    @Override
    public void updateGoods(GoodsParam goodsParam) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        try {
            runner.update("update goods set img = ?,name = ?,price = ?,typeId = ?,stockNum = ?,`desc` = ? where id = ?",
                    goodsParam.getImg(),
                    goodsParam.getName(),
                    goodsParam.getPrice(),
                    goodsParam.getTypeId(),
                    goodsParam.getStockNum(),
                    goodsParam.getDesc(),
                    goodsParam.getId());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取留言中的信息数据并封装
     * @return
     */
    @Override
    public List<Message> getNoReplyMsg() {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        List<Message> messages = null;
        try {
            messages = runner.query("select * from message where state = ?",
                    new BeanListHandler<Message>(Message.class), 1);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return messages;
    }

    /**
     * 获取留言者的昵称
     * @return
     */
    @Override
    public String getUserName(Integer userId) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        String userName = null;
        try {
            userName = runner.query("select nickname from user where id = ?",
                    new ScalarHandler<>(), userId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userName;
    }

    /**
     * 获取留言对象的商品名称
     * @param goodsId
     * @return
     */
    @Override
    public String getGoodsName(Integer goodsId) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        String GoodsName = null;
        try {
            GoodsName = runner.query("select name from goods where id = ?",
                    new ScalarHandler<>(), goodsId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return GoodsName;
    }

    /**
     * 获取回复留言的数据并封装
     * @return
     */
    @Override
    public List<ReplyMessage> getRepliedMsg() {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        List<ReplyMessage> messages = null;
        try {
            messages = runner.query("select * from message where state = ?",
                    new BeanListHandler<ReplyMessage>(ReplyMessage.class), 0);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return messages;
    }

    /**
     * 获取回复的内容(回复内容和时间)
     * @param id
     * @return
     */
    @Override
    public Reply getReply(Integer id) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        Reply reply = null;
        try {
            reply = runner.query("select time from message where id = ?",
                    new BeanHandler<>(Reply.class), id);
            String content = runner.query("select replyContent from message where id = ?",
                    new ScalarHandler<>(), id);
            reply.setContent(content);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return reply;
    }

    /**
     * 获取客户端上问答信息的显示
     * @param id
     * @return
     */
    @Override
    public List<ClientMessage> getGoodsMsg(String id) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        List<ClientMessage> messages = null;
        try {
            messages = runner.query("select id,content from message where goodsId = ?",
                    new BeanListHandler<ClientMessage>(ClientMessage.class), id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return messages;
    }

    /**
     * 通过id获取留言的时间
     * @param id
     * @return
     */
    @Override
    public String getCreateTime(Integer id) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        Object time = null;
        try {
            time = runner.query("select createtime from message where id = ?",
                    new ScalarHandler<>(), id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return time.toString();
    }

    /**
     * 通过messageId获取UserId
     * @return
     */
    @Override
    public Integer getUserId(Integer id) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        Integer userId = null;
        try {
            userId = runner.query("select userId from message where id = ?",
                    new ScalarHandler<>(), id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userId;
    }

    /**
     * 向message表中添加数据
     * @param message
     * @return
     */
    @Override
    public int askGoodsMsg(AskMessage message) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        Map<String, Object> map = getInsertMessage(message);
        String sql = (String) map.get("sql");
        List<Object> params = (List<Object>) map.get("params");
        int row = 0;
        try {
            row = runner.update(sql, params.toArray());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }

    /**
     * 获取添加到message表的sql语句
     * @param message
     * @return
     */
    private Map<String, Object> getInsertMessage(AskMessage message) {
        Map<String, Object> map = new HashMap<>();
        List<Object> params = new ArrayList<>();
        String baseSql = "insert into message ";
        baseSql = baseSql + "(userId ,";
        params.add(getUserId(message.getToken()));
        baseSql = baseSql + "goodsId ,";
        params.add(message.getGoodsId());
        baseSql = baseSql + "content ,";
        params.add(message.getMsg());
        baseSql = baseSql + "state ,";
        params.add(1);
        baseSql = baseSql + "goodsName ,";
        params.add(getGoodsName(Integer.parseInt(message.getGoodsId())));
        baseSql = baseSql + "username) ";
        params.add(message.getToken());
        baseSql = baseSql + "values(?,?,?,?,?,?)";
        map.put("sql",baseSql);
        map.put("params",params);
        return map;
    }

    /**
     * 通过nickname得到userId
     * @param token
     * @return
     */
    private Object getUserId(String token) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        Object userId = null;
        try {
            userId = runner.query("select id from user where nickname = ?",
                    new ScalarHandler<>(), token);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userId;
    }


    /**
     * 只需要更改对应的状态和添加回复内容即可
     * @param replyMessage
     */
    @Override
    public void reply(ReplyMessage replyMessage) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        try {
            int update = runner.update("update message set state = ?,replyContent = ? where id = ?",
                    0, replyMessage.getContent(), replyMessage.getId());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取客户端商品的信息
     * @param id
     * @return
     */
    @Override
    public ClientGoods getGoodsInfo(String id) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        ClientGoods goods = null;
        try {
            goods = runner.query("select * from goods where id = ?",
                    new BeanHandler<>(ClientGoods.class), id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goods;
    }

}
